package cn.com.nes.site.module.dao.analysis;

import cn.com.nes.common.util.*;
import cn.com.nes.site.entity.DataReq;
import cn.com.nes.site.entity.OtherData;
import cn.com.nes.site.entity.PageData;
import cn.com.nes.site.module.dao.BaseDaoImpl;
import com.alibaba.fastjson2.JSONArray;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

@Repository
public class AppAnalysisDaoImpl extends BaseDaoImpl {
	public PageData getAppHomeData(Connection conn,DataReq req) {
        Map<String,Object> extData = new LinkedHashMap<String,Object>();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("获取待办失败");
        try {
        	//参考getWillDeal方法
            StringBuffer sb = new StringBuffer();
			StringBuffer sb1 = new StringBuffer();
			//区分是否为管理员 '04'是管理员
			String userType=req.getUinfo().getUserType();
			if(!"04".equals(userType)){
				sb1.append(" and ((1=1  ");
				sb1.append(XzqhUtil.getMultipleQueryXzqh(this.getJdbcTemplate_newg(), req.getUinfo().getAreacode(), "a.xzqhdm"));
				sb1.append(" ) ");
				sb1.append(" or a.clueowner='").append(req.getUinfo().getId()).append("' ");
				sb1.append(" ) ");
			}
            /*
            //线索未挖掘
            sb.delete(0, sb.length());
            sb.append("select if(max(a.createTime)<date_sub(now(),INTERVAL 30 day),1,0) flag from clue a where a.isdelete=0  ")
            .append(" and a.cluebelongtocompany like concat('"+req.getUinfo().getCompanyId()+"','%') and ((1=1  ");
		    sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, req.getUinfo().getAreacode(),"a.area_code"));
		    
		    sb.append(" ) or a.clueowner='").append(req.getUinfo().getId()).append("'  ) ");
            sb = DBUtils.sql_check(sb) ;
            List<Map<String, Object>> wwjClueList = this.queryForList(conn,sb.toString(), new Object[]{});
            extData.put("wwjClueCount", wwjClueList.get(0).get("flag")); //未挖掘商机
            //线索待跟进
            sb.delete(0, sb.length());
            sb.append("select count(distinct l.uuid) cnt " +
					" from( " +
					" select b.uuid,b.cluebelongtocompany,b.clueowner,b.xzqhdm,max(a.createTime) maxTime,1 type " +
					" from log_operate a  " +
					" left join clue b on a.clueId=b.uuid " +
					" where b.clueowner='").append(req.getUinfo().getId()).append("' and b.followupstatus!='03' " +
					" group by b.uuid " +
					" union  " +
					" select b.uuid,b.cluebelongtocompany,b.clueowner,b.xzqhdm,max(a.modifyTime) maxTime,2 type " +
					" from log_claim a  " +
					" left join clue b on a.clueId=b.uuid " +
					" where b.clueowner='").append(req.getUinfo().getId()).append("' and b.followupstatus!='03' " +
					" group by b.uuid " +
					" ) l where l.maxTime<date_sub(curdate(),INTERVAL 7 day) and l.cluebelongtocompany like concat('"+req.getUinfo().getCompanyId()+"','%') and ((1=1");
            sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, req.getUinfo().getAreacode(),"a.area_code"));
			sb.append(" ) or l.clueowner='").append(req.getUinfo().getId()).append("'  ) ");
            sb = DBUtils.sql_check(sb) ;
            List<Map<String, Object>> dgjClueList = this.queryForList(conn,sb.toString(), new Object[]{});
            extData.put("dgjClueCount", dgjClueList.get(0).get("cnt"));
            */ 
            
            //商机未跟进
            sb.delete(0, sb.length());
            sb.append("select count(1) cnt from opportunity a where a.isdelete=0 ")
            .append(" and a.uuid not in (select a.opportunityId from log_claim a where a.isdelete=0 and a.type='03' ) ");
			sb.append(" and a.companyId like concat('" + req.getUinfo().getCompanyId() + "','%') ");
//			if(Globals.DEMO_COMPANYID.equals(req.getUinfo().getCompanyId())) {//演示账号  后面如果需要再加（下面雷同）
//				sb.append(" and a.companyId='").append(req.getUinfo().getCompanyId()).append("'  ");
//			}else {
//				sb.append(" and a.userid='").append(req.getUinfo().getId()).append("' ");
//			}
			if(!"04".equals(userType)){
				sb.append(" and a.opportunityowner='").append(req.getUinfo().getId()).append("' ");
			}
            sb = DBUtils.sql_check(sb) ;
            List<Map<String, Object>> wgjOpportinityList = this.queryForList(conn,sb.toString(), new Object[]{});
            extData.put("opportinity_notFollow", wgjOpportinityList.get(0).get("cnt"));

			//客户待拜访(以改为线索待跟进)
			sb.delete(0, sb.length());
			sb.append(" select count(distinct l.uuid) cnt from( ");
			sb.append(" select b.uuid,b.cluebelongtocompany,b.clueowner,b.xzqhdm,max(a.createTime) maxTime,1 type " +
					" from log_operate a  " +
					" left join clue b on a.clueId=b.uuid " +
					" where b.followupstatus!='03' and sfzx='1' AND b.clueowner IS NOT NULL and b.isdelete = '0' ");
			if(!"04".equals(userType)){
				sb.append(" and a.userid='").append(req.getUinfo().getId()).append("' ");
			}
			sb.append(" group by b.uuid ");
			sb.append(" union ");
			sb.append(" select b.uuid,b.cluebelongtocompany,b.clueowner,b.xzqhdm,max(a.modifyTime) maxTime,2 type " +
					" from log_claim a  " +
					" left join clue b on a.clueId=b.uuid " +
					" where b.followupstatus!='03' AND b.clueowner IS NOT NULL and b.isdelete = '0' ");
			if(!"04".equals(userType)){
				sb.append(" and a.userid='").append(req.getUinfo().getId()).append("' ");
			}
			sb.append(" group by b.uuid ) l where l.maxTime<date_sub(curdate(),INTERVAL 30 day) and l.cluebelongtocompany like concat('" + req.getUinfo().getCompanyId() + "','%') ");
			//sb = DBUtils.sql_check(sb) ;
            List<Map<String, Object>> dbfCustomerList = this.queryForList(conn,sb.toString(), new Object[]{});
            extData.put("waitVisitedCustomer", dbfCustomerList.get(0).get("cnt"));

			//线索未分配
			sb.delete(0, sb.length());
			sb.append("select count(1) cnt from clue a where a.isdelete=0 and sfzx='1' and a.culestate='02' and a.cluebelongtocompany like concat('" + req.getUinfo().getCompanyId() + "','%') ");
			sb.append(sb1);
			sb = DBUtils.sql_check(sb);
			List<Map<String, Object>> wfpClueList = this.queryForList(conn,sb.toString(), new Object[]{});
			extData.put("wfpClueCount", wfpClueList.get(0).get("cnt"));

/*            //商机无进展
            sb.delete(0, sb.length());
            sb.append("select count(distinct l.uuid) cnt " +
					" from( " +
					" select b.uuid,b.companyId,b.opportunityowner,b.xzqhdm,max(a.createTime) maxTime,1 type " +
					" from log_operate a  " +
					" left join opportunity b on a.opportunityId=b.uuid " );
            
            if(Globals.DEMO_COMPANYID.equals(req.getUinfo().getCompanyId())) {//演示账号
            	sb.append(" where b.companyId='").append(req.getUinfo().getCompanyId()).append("'  ");
            }else {
            	sb.append(" where b.opportunityowner='").append(req.getUinfo().getId()).append("' ");
            }
            
            
            sb.append(" and b.opportunitystage!='06' and b.opportunitystage!='07' " +
					" group by b.uuid " +
					" union  " +
					" select b.uuid,b.companyId,b.opportunityowner,b.xzqhdm,max(a.modifyTime) maxTime,2 type " +
					" from log_claim a  " +
					" left join opportunity b on a.opportunityId=b.uuid " );
            
            
            if(Globals.DEMO_COMPANYID.equals(req.getUinfo().getCompanyId())) {//演示账号
            	sb.append(" where b.companyId='").append(req.getUinfo().getCompanyId()).append("'  ");
            }else {
            	sb.append("  where b.opportunityowner='").append(req.getUinfo().getId()).append("' ");
            }
            
            sb.append(" and b.opportunitystage!='06' and b.opportunitystage!='07' " +
					" group by b.uuid " +
					" ) l where l.maxTime<date_sub(curdate(),INTERVAL 7 day) and l.companyId like concat('"+req.getUinfo().getCompanyId()+"','%') ");
            sb = DBUtils.sql_check(sb) ;
            List<Map<String, Object>> wjzOpportunityList = this.queryForList(conn,sb.toString(), new Object[]{});
            extData.put("noProgressOpportunity", wjzOpportunityList.get(0).get("cnt"));*/
            
            //商机新增趋势
            sb.delete(0, sb.length());
            sb.append(" SELECT date_format(a.createTime,'%Y-%m-%d') d,count(1) cnt  from opportunity a ");
            sb.append(" where a.companyid like concat(?,'%')   ");
            sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, req.getUinfo().getAreacode(),"a.xzqhdm"));
            sb.append(" GROUP BY date_format(a.createTime,'%Y-%m-%d') ");
            sb.append(" ORDER BY date_format(a.createTime,'%Y-%m-%d') ");
            List<Map<String, Object>> opportunityList = this.queryForList(conn,sb.toString(), new Object[]{req.getUinfo().getCompanyId()});
            extData.put("opportunityList", opportunityList);
            
            
            //项目信息概况
			sb.delete(0, sb.length()) ;
			sb.append(" select count(1) allcnt,sum(if(project_status in ('04','05','06'),1,0)) inrunning,sum(if(a.createTime>date_sub(now(), INTERVAL 30 DAY),1,0)) cnt30, sum(if(is_payment='1',1,0)) payment, sum(if(is_payment='0' and a.project_status='07' ,1,0)) notpayment, ");
			sb.append(" sum(if(b.end_time<now()  and a.project_status<'07',1,0)) out_of_date_cnt ");
			sb.append(" from t_project a LEFT JOIN t_project_scheduling b on a.uuid=b.project_uuid where (a.company_id like concat(?,'%') or a.manufacture_id='").append(req.getUinfo().getCompanyId()).append("')  ");
			
			sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, req.getUinfo().getAreacode(),"a.area_code"));
			
			if(!StringUtils.isEmpty(req.getUinfo().getProjectIds())) {
				sb.append("  and a.id in ").append(req.getUinfo().getProjectIds());
			}
			List<Map<String, Object>> list = queryForList(conn,sb.toString(),
							new Object[] {req.getUinfo().getCompanyId() });
			if(list.size()>0) {
				extData.put("projectInfo", list.get(0)) ;
			}
			
			//按天获取新建项目曲线图
			sb.delete(0, sb.length()) ;
			sb.append(" SELECT date_format(a.createTime,'%Y-%m-%d') createTime,sum(if(a.project_type='01',1,0)) civilCnt,sum(if(a.project_type<>'01',1,0)) commercialCnt  from t_project a  where (a.company_id like concat(?,'%') or a.manufacture_id='").append(req.getUinfo().getCompanyId()).append("')  ");
			sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, req.getUinfo().getAreacode(),"a.area_code"));
			
			
			if(!StringUtils.isEmpty(req.getUinfo().getProjectIds())) {
				sb.append("  and a.id in ").append(req.getUinfo().getProjectIds());
			}
			
			//本月散点图
			sb.append(" and a.createTime>= '").append(DBUitl.getDate(-90)).append("' ");
			
			sb.append("  group by date_format(a.createTime,'%Y-%m-%d')" );
			sb.append("  order by a.createTime " );
			list = queryForList(conn,sb.toString(),
							new Object[] {req.getUinfo().getCompanyId() });
			extData.put("newProjectLine", list) ;
            
			//数据散点图
			sb.delete(0, sb.length()) ;
			sb.append(" SELECT a.uuid project_uuid, a.charge,a.project_id,a.project_name,a.project_status,a.latitude,a.longitude,date_format(a.createTime,'%Y-%m-%d %T') createTime  FROM 	t_project a INNER JOIN t_param b on a.project_status=b.`value` and b.param_type='90'  where (a.company_id like concat(?,'%') or a.manufacture_id='").append(req.getUinfo().getCompanyId()).append("')  ");
			sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, req.getUinfo().getAreacode(),"a.area_code"));
			sb.append(" and a.latitude is not null and a.longitude is not null ");
			if(!StringUtils.isEmpty(req.getUinfo().getProjectIds())) {
				sb.append("  and a.id in ").append(req.getUinfo().getProjectIds());
			}
			sb.append(" and a.createTime>= '").append(DBUitl.getDate(-30)).append("' ");
			
			list = queryForList(conn,sb.toString(),
							new Object[] {req.getUinfo().getCompanyId() });
			extData.put("mapData", list) ;	
			
			//施工中项目列表
			sb.delete(0, sb.length()) ;
			sb.append(" SELECT a.uuid project_uuid,	a.project_id project_no,a.charge,a.project_id,a.project_name,a.project_status,a.latitude,a.longitude,date_format(a.createTime,'%Y-%m-%d %T') createTime,date_format(a.planned_start_time,'%Y-%m-%d %T') planned_start_time,date_format(a.planned_end_time,'%Y-%m-%d %T') planned_end_time,date_format(c.start_time,'%Y-%m-%d %T') start_time,date_format(c.end_time,'%Y-%m-%d %T') end_time,b.name project_status_name,e.departmentName  FROM 	t_project a INNER JOIN t_param b on a.project_status=b.`value` and b.param_type='90'  LEFT JOIN t_project_scheduling c on a.uuid=c.project_uuid  LEFT JOIN t_department e on a.department_id=e.id  where (a.company_id like concat(?,'%') or a.manufacture_id='").append(req.getUinfo().getCompanyId()).append("')  ");
			//sb.append("  and a.area_code  like '").append(XzqhUtil.getQueryXzqh(this.getJdbcTemplate_newg(), req.getUinfo().getAreacode())).append("%' ");
			sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, req.getUinfo().getAreacode(),"a.area_code"));
			sb.append(" and a.latitude is not null and a.longitude is not null and a.project_status<>'07' ");
			if(!StringUtils.isEmpty(req.getUinfo().getProjectIds())) {
				sb.append("  and a.id in ").append(req.getUinfo().getProjectIds());
			}
			
			sb.append(" limit 3 ");
			list = queryForList(conn,sb.toString(),
							new Object[] {req.getUinfo().getCompanyId() });
			extData.put("constructioningList", list) ;	
			
            res.setExtData(extData);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

        }
        return res;
    }
	
	public PageData getOpeMapData(Connection conn,DataReq req) {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("获取数据失败");
        StringBuffer sb = new StringBuffer();
        StringBuffer sbfrom = new StringBuffer();
        try {
            sb.append("						 select iei.sb_id                                                                                  as sbId,         ");
            sb.append("              if(iei.zbq, datediff(iei.zbq, now()), 0)                                                   as sbxlh,             ");
            sb.append("              iei.wx_id                                                                                  as wxId,              ");
            sb.append("              iei.dd_id                                                                                  as ddId,              ");
            sb.append("              iii.xmlxr                                                                                  as name,              ");
            sb.append("              iii.xmlxrdh                                                                                as phone,             ");
            sb.append("              iii.xmdz                                                                                   as address,           ");
            sb.append("              idvi.title                                                                                 as sblx,              ");
            sb.append("              iei.sblx_id                                                                                as sblxId,            ");
            sb.append("              ts.sbxh,                                                                                                         ");
            sb.append("              if(ind.is_online = 0, '在线', '离线')                                                          as isOnline,      ");
            sb.append("              if(ind.is_online = 0, if(ind.fault_code != '', '故障', if(ind.is_warn = 1, '预警', '正常')), '') as state,       ");
            sb.append("              if(ind.is_online = 0, if(ind.a350 = 0, '关机', '开机'), '')                                       onOff,         ");
            sb.append("              iui.xm                                                                                     as latestEditor,      ");
            sb.append("              iei.xgsj                                                                                   as latestUpdateTime,  ");
            sb.append("              iei.alias,                                                                                                       ");
            sb.append("              iii.xzqh,                                                                                                        ");
            sb.append("              tx.xzqhmc,                                                                                                       ");
            //sb.append("              if(iuf.id is null, 0, 1)                                                                   as isFollow,          ");
            sb.append("              ind2.gmt_create                                                                            as cjsj,              ");
            sb.append("              iii.jd,                                                                                                          ");
            sb.append("              iii.wd,                                                                                                          ");
            sb.append("              REPLACE(tx.xzqhqc, '->', '')                                                                  xzqhqc,            ");
            sb.append("              date_format(iei.zbq, '%Y-%m-%d')                                                              zbrq,              ");
            sb.append("              date_format(iii.cjsj, '%Y-%m-%d')                                                             installDate,       ");
            sb.append("              ts.sblxmc,                                                                                                       ");
            sb.append("              iei.sbxlh                                                                                     serialnumber,      ");
            //sb.append("              wxsb.wx_kh,                                                                                                      ");
            //sb.append("              date_format(wxsb.cjsj, '%Y-%m-%d')                                                            dtuRegDate,        ");
            sb.append("              b.`name`                                                                                      protocolName,      ");
            sb.append("              iei.protocol_id                                                                            as protocolId,        ");
            sb.append("              if(DATE_SUB(now(), INTERVAL 30 MINUTE) > ind2.gmt_create, 0, 1)                               devStatus,         ");
            sb.append("              if(ind2.is_online = 0, ind2.fault_codes, '')                                               as faultCodes,        ");
            sb.append("              iei.dealer_name                                                                            as dealerName,        ");
            sb.append("               iei.dealer_phone                                                                           as dealerPhone,       ");
            sb.append("             iui.user_id                                                                                as createUserId       ");
            sb.append("  from nes_ope_prod.is_equipment_info iei ");
            sb.append("           inner join nes_ope_prod.is_install_info iii on (iii.dd_id = iei.dd_id and iii.sfsc = 0)                                           ");   
            sb.append("           left join nes_ope_prod.is_device_protocol_info b on b.protocol_id = iei.protocol_id                                               ");
            sb.append("           left join nes_ope_prod.t_sblx ts on (ts.sblx_id = iei.sblx_id and ts.sfsc = 0)                                                    ");
            sb.append("           left join nes_ope_prod.is_dict_value_info idvi on idvi.type_id = 'd_type_2' and idvi.dic_no = ts.sblx                             ");
            sb.append("           left join nes_ope_prod.is_norm_data_b ind on ind.dtu_id = iei.wx_id and ind.sb_id = iei.sb_id                                     ");
            sb.append("       left join nes_ope_prod.is_norm_data ind2 on ind2.dtu_id = iei.wx_id and ind2.sb_id = iei.sb_id                                        ");
            sb.append("       left join nes_ope_prod.is_user_info iui on iui.user_id = iei.create_oper                                                              ");
            sb.append("       left join nes_ope_prod.t_xzqh tx on tx.xzqhdm = iii.xzqh                                                                              ");
            //sb.append("       left join nes_ope_prod.is_wxsb wxsb on iei.wx_id = wxsb.wx_id                                                                         ");
            //sb.append("       left join nes_ope_prod.is_user_follow iuf on iuf.userId = '0' and iuf.sbId = iei.sb_id                                          ");
            sb.append("       where iei.sfsc = 0                                                                                                       ");
            sb.append("         and iei.sfzj != 2                                                                                                      ");
            
            //行政区划过滤
            sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, req.getUinfo().getAreacode(),"iii.xzqh"));
            
            if("01".equals(req.getUinfo().getCompanyType())) {//如果是厂家
            	sb.append(" and iii.manufacture_id = ? ") ;
            }else {//代理商
            	sb.append(" and '1'=? ") ;
            }
            
            String sbId = (String) req.getReqData().get("sbId") ; 
            if(!StringUtils.isEmpty(sbId)) {
            	sb.append(" and iei.sb_id =  '").append(sbId).append("' ") ;
            }
            
            
            String state = (String) req.getReqData().get("state") ;
            if(!StringUtils.isEmpty(state)) {
            	switch (state) {
				case "5"://离线
					sb.append(" and (ind.is_online = 1 or ind.is_online is null or ind2.gmt_create<DATE_ADD(now(),INTERVAL -60 MINUTE)) ") ;
					break;
				case "4"://在线故障
					sb.append(" and ind.fault_code <> '' and ind.is_online = 0 ") ;
					break;
				case "3"://在线正常
					sb.append(" and ind.fault_code is null and  ind.is_online = 0 ") ;
					break;
				case "2"://关机正常
					sb.append(" and ind.a350 = 0 and ind.is_online = 0 ") ;
					break;
				case "1"://开机正常
					sb.append(" and ind.a350 > 0 and ind.is_online = 0 ") ;
					break;

				default:
					break;
				}
            }
            
  
            sbfrom.append(sb.toString());
            sb = DBUitl.sql_check(sb) ;
			int idx = sbfrom.indexOf(" from ");
			sbfrom.delete(0, idx);
			Page page = new Page(conn,sbfrom.toString(), req.getPageid(), req.getPagesize(), res,new Object[] { req.getUinfo().getNes_companyId() });
			
			sb.append(" order by ifnull(ind.is_online, 1), iei.xgsj desc ");
			sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
			
            
            
            List<Map<String, Object>> list = queryForList(conn,sb.toString(),
					new Object[] {req.getUinfo().getNes_companyId() });
           
            
            res.setPage(list);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

        }
        return res;
    }
	
	public PageData getNewParameters(Connection conn,DataReq req) throws SQLException {
		PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("获取数据失败");
        StringBuffer sb = new StringBuffer();
		try {
			String sbId = (String)req.getReqData().get("sbId") ;
			sb.delete(0, sb.length()) ;
			sb.append("SELECT distinct");
			sb.append(" d.field_name AS dataItem, ");
			sb.append(" a.sb_id sbId,");
			sb.append(" a.alias,");
			sb.append(" IF(c.fore_name != '', c.fore_name, c.name) dataName, ");
			sb.append(" c.classify AS classifyId, ");
			sb.append(" c.unit_value AS unitValueId, ");
			sb.append(" unitDict.title AS unitValueName, ");
			sb.append(" classifyDict.title AS classifyName, ");
			sb.append(" c.data_item_id AS dataItemId ");
			//sb.append(" ncf.sort, ifnull(ncf.isShow, 1) isShow ");
			sb.append(" FROM ");
			sb.append(" nes_ope_prod.is_device_protocol_data_item_info c ");
			sb.append(" INNER JOIN nes_ope_prod.is_data_item_template_info d ON c.template_id=d.template_id and d.field_name not like 'temp%' ");
			sb.append(" AND d.is_deleted = 0 AND c.is_deleted = 0 ");
			sb.append(" INNER JOIN nes_ope_prod.is_equipment_info a ON a.protocol_id = c.protocol_id ");
			sb.append(" LEFT JOIN nes_ope_prod.is_dict_value_info unitDict ON unitDict.type_id = 'd_type_28' AND unitDict.dic_no = c.unit_value AND unitDict.sfsc = 0 ");
			sb.append(" AND unitDict.sfty = 0 ");
			sb.append(" LEFT JOIN nes_ope_prod.is_dict_value_info classifyDict ON 'd_type_33' = classifyDict.type_id AND classifyDict.dic_no = c.classify ");
			sb.append(" AND classifyDict.manufacture_id = c.manufacture_id AND classifyDict.sfty = 0 AND classifyDict.sfsc = 0 ");
			//sb.append(" INNER JOIN nes_ope_prod.is_device_item x on x.item_id = c.data_item_id  and x.is_deleted='0' ");
			sb.append(" WHERE ");
			sb.append("  a.sb_id = ? ");
			//sb.append("  and x.sb_id='").append(sbId).append("'");
			sb.append(" order by  d.field_name ");
			List<Map<String, Object>> pageList = queryForList(conn,sb.toString(),
					new Object[] {sbId });
			res.setPage(pageList);

			//获取所有分类
			List<Map<String, Object>> classList = new ArrayList<Map<String, Object>>();
			for(int i=0;i<pageList.size();i++){
				LinkedHashMap<String, Object> obj = (LinkedHashMap<String, Object>) pageList.get(i) ;
				LinkedHashMap<String, Object> map = new LinkedHashMap<String, Object>();
				map.put("classifyId", obj.get("classifyId")) ;
				map.put("classifyName", obj.get("classifyName")) ;
				//map.put("unitValueId", obj.get("unitValueId")) ;
				//map.put("unitValueName", obj.get("unitValueName")) ;
				if(!classList.contains(map)) {
					classList.add(map) ;
				}
			}

			Map<String, Object> extData = new LinkedHashMap<String, Object>();
			extData.put("classList", classList) ;
			res.setExtData(extData);

			//获取采集数据
			sb.delete(0, sb.length()) ;
			sb.append("SELECT a.sb_id, date_format(a.gmt_create, '%Y-%m-%d %T') gmt_create, a.fault_code, a.fault_codes, a.other_data from nes_ope_prod.is_norm_data a, nes_ope_prod.is_equipment_info b where b.sb_id = a.sb_id and b.wx_id = a.dtu_id and b.sb_id = ? and a.other_data is not null ");
			pageList = queryForList(conn,sb.toString(),
					new Object[] {sbId });
			for(int i=0;i<pageList.size();i++) {
				Map<String, Object> e = pageList.get(i) ;
				String other_data = (String) e.get("other_data") ;
				other_data = ZipUtils.gunzip(other_data);
				System.out.println(other_data);
				List<OtherData> otherDataList = JSONArray.parseArray(
						other_data, OtherData.class);
				for (OtherData otherData : otherDataList) {
					e.put(otherData.getF(), otherData.getV()) ;
				}
			}
			
			extData.put("data", pageList) ;
			
		} finally {
			
		}
		res.setRetCode(0);
		res.setRetMessage("OK");
		return res;
	}	
	
	
}

